package de.wefunkdroid.core;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import android.util.Pair;
import de.wefunkdroid.core.data.Show;
import de.wefunkdroid.core.data.Track;

public class SQLiteHandler {
	SQLiteDatabase notedb;
	private boolean isconnected = false;
	private static final String dbname = "wefunkdb";
	private static final String showtable = "stable";
	private static final String showID= "sid";
	private static final String tracktable = "ttable";
	private static final String trackID = "tid";
	private static final String trackName = "tname";
	private static final String trackArtist = "tart";
	private static final String showNum = "snum";
	private static final String showDate = "sdate";
	private static final String trackMsPos="mspos";
	
	private Context context;

	/**
	 * Konstruktor des SQLiteHandlers wird mit einem Kontext initialisiert, der
	 * ben&ouml;tigt wird um zugriff auf die Datenbank der Applikation zu
	 * erhalten
	 * 
	 * @param appcontext
	 *            Der Applikationskontext <br>
	 * <br>
	 *            <b>Es wird keine Verbindung hergestellt </b>
	 */
	public SQLiteHandler(Context appcontext) {
		context = appcontext;
	}

	/**
	 * Stellt eine Verbindung zur Kommunikation mit der Datenbank bereit
	 */
	public void connectDB() {
		if (!isconnected) {
			isconnected = true;
			try{
			notedb = context.openOrCreateDatabase(dbname, Context.MODE_PRIVATE,
					null);
			notedb.execSQL("CREATE TABLE IF NOT EXISTS "+ showtable + "("+ showID+" INTEGER PRIMARY KEY ASC AUTOINCREMENT,"
					+ showNum + " TEXT UNIQUE NOT NULL," + showDate
					+ " TEXT NOT NULL);");
			notedb.execSQL("CREATE TABLE IF NOT EXISTS "+ tracktable + "("
					+ trackID + " INTEGER PRIMARY KEY ASC AUTOINCREMENT," + trackArtist
					+ " TEXT NOT NULL," + trackName + " TEXT NOT NULL," + showNum + " TEXT NOT NULL, "+trackMsPos+" TEXT NOT NULL);");
			}catch (Exception e) {
				Log.e("SQLiteHandler", e.getCause().toString());
			}
		}

	}

	/**
	 * Erstellt die Tabellen dieses Projektes neu
	 */
	public void recreateTable() {
		if (isconnected) {

			notedb.execSQL("DROP TABLE " + showtable);
			notedb.execSQL("DROP TABLE " + tracktable);
			notedb.execSQL("CREATE TABLE IF NOT EXISTS "+ showtable + "("+ showID+" INTEGER PRIMARY KEY ASC AUTOINCREMENT,"
					+ showNum + " TEXT UNIQUE NOT NULL," + showDate
					+ " TEXT NOT NULL);");
			notedb.execSQL("CREATE TABLE IF NOT EXISTS "+ tracktable + "("
					+ trackID + " INTEGER PRIMARY KEY ASC AUTOINCREMENT," + trackArtist
					+ " TEXT NOT NULL," + trackName + " TEXT NOT NULL," + showNum + " TEXT NOT NULL, "+trackMsPos+" TEXT NOT NULL);");

		}
	}

	/**
	 * Schlie&szlig;t eine bestehende Verbindung zur Datenbank. Die Kommikation
	 * ist beendet
	 */
	public void closeConnection() {
		if (isconnected) {
			isconnected = false;
			notedb.close();
		}
	}
	
	public void insertOrIgnoreShow(Show s) {
		try {
			notedb.execSQL("INSERT OR IGNORE INTO " + showtable + " VALUES(NULL,'"
					+ s.getNum() + "','" + s.getDate() + "');");
			int i = s.getTracks().size();
			ArrayList<Track> trk = s.getTracks();
			Track t;
			for(int j = 0; j<i;j++){
				t= trk.get(j);
				notedb.execSQL("INSERT OR IGNORE INTO " + tracktable + " VALUES(NULL,'"
						+ t.getArtist() + "','" + t.getTrack() + "','"+s.getNum()+"','"+t.getMsPos()+"');");
				
			}
		} catch (Exception ex) {
			ex.printStackTrace();

		}

	}
	
    private Show getShow(String shownum){
		String date = "";
		ArrayList<Track> tracks = getTracks(shownum);
		Cursor crs = notedb.query(showtable, null, "snum like '" + shownum + "'",null, null, null, null);
		if (crs != null) {
			int dateidx=crs.getColumnIndex(showDate);
			if (crs.moveToNext()) {
				date = crs.getString(dateidx);
			}
		}
		Show s=new Show(String.valueOf((Integer.valueOf(shownum)-168)),date,tracks);
		return s;
	}
    
    private ArrayList<Track> getTracks(String showNum){
    	ArrayList<Track> res = new ArrayList<Track>();
    	Cursor crs=notedb.query(tracktable, null, "snum like '" + showNum + "'", null, null, null, null);
    	int artI = crs.getColumnIndex(trackArtist);
    	int trackI=crs.getColumnIndex(trackName);
    	int posI = crs.getColumnIndex(trackMsPos);
		while (crs.moveToNext()) {
			res.add(new Track(crs.getString(artI),crs.getString(trackI),crs.getString(posI)));
		}
    	return res;
    }
	
	
	public Pair<Show, Integer> search(String str){
		String erg = "";
		int pos = 0;
		Cursor crs = notedb.query(tracktable, new String[]{showNum, trackID}, "tart like '" + str+"'", null, null, null, null);
		if (crs != null) {
			int noteIndex = crs.getColumnIndex(showNum);
			int tIndex = crs.getColumnIndex(trackID);
			if (crs.moveToNext()) {
				erg = crs.getString(noteIndex);
				pos = crs.getInt(tIndex);
			}
		}
		Show s = getShow(erg);
		return new Pair<Show, Integer>(s, pos);	
	}

}